package com.fzy.core.system.service;

//import ge.app.service.RoleListMapper;
import ge.core.system.service.DictEntryListVo;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.RowMapper;

import com.fzy.core.BaseService;
import com.fzy.core.entity.City;
import com.fzy.core.entity.District;
import com.fzy.core.entity.Province;
import com.fzy.core.entity.Resource;
import com.fzy.core.entity.Rssc;
import com.fzy.core.utils.RsUtils;
import com.fzy.core.utils.SqlUtils;

public class MemoryDataService extends BaseService {
	protected final Logger log = Logger.getLogger(getClass());
	
	public  List getProvinces() {
				
		String sql = "";
		sql += " SELECT   *";
		sql += "   FROM (SELECT vprovcode, vprovname, vprovshortname, vprovabb,";
		sql += "                visduchy, " + SqlUtils.toNumber("vdisporder")
				+ " o";
		sql += "           FROM tdn_province";
		sql += "          WHERE vprovcode <> '00' and vprovcode <> '60' and vprovcode <> '61' and vprovcode <> '43')";
		sql += " ORDER BY o";
		List provinces =query(sql,
				new Object[] {}, new RowMapper() {
					public Object mapRow(ResultSet rs, int i)
							throws SQLException {
						Province province = new Province();
						province.setAbbr(rs.getString("VPROVABB"));
						province.setIsDuchy(rs.getString("VISDUCHY"));
						province.setShortName(rs.getString("VPROVSHORTNAME"));

						province.setCode(rs.getString("VPROVCODE"));
						province.setName(rs.getString("VPROVNAME"));
						return province;
					}
				});
		return provinces;
	}
	/***
	 * 城市列表中增加直辖市
	 * @return
	 */
	public List getCities() {
				
		String sql = "";
		sql += " SELECT   *";
		sql += "     FROM (SELECT vcitycode, vcityname, vcityshortname, vprovcode,";
		sql += "                " + SqlUtils.toNumber("vdisporder") + " o";
		sql += "             FROM tdn_city";
		sql += "            WHERE vcitycode <> '0' AND vprovcode <> '00' " ;
		sql += "   and vprovcode not in (select a.vprovcode  from tdn_province a  where visduchy='1' and vdisplay='1')" ;
		sql += "  union all ( select vprovcode||'_'||vprovcode,vprovname,vprovabb,vprovcode, "+ SqlUtils.toNumber("vdisporder") + " o   from  tdn_province a  where VISDUCHY='1' and VDISPLAY='1'))";
		sql += " ORDER BY vprovcode, o";
		List cities = query(sql,
				new Object[] {}, new RowMapper() {
					public Object mapRow(ResultSet rs, int i)
							throws SQLException {
						City city = new City();
						city.setShortName(rs.getString("VCITYSHORTNAME"));
						city.setCode(rs.getString("VCITYCODE"));
						city.setName(rs.getString("VCITYNAME"));
						city.setParentCode(rs.getString("VPROVCODE"));
						return city;
					}
				});
		return cities;
	}
	/***
	 * 城市列表中不包含直辖市
	 * @return
	 */
	public List getCities_2() {
		
		String sql = "";
		sql += " SELECT   *";
		sql += "     FROM (SELECT vcitycode, vcityname, vcityshortname, vprovcode,";
		sql += "                " + SqlUtils.toNumber("vdisporder") + " o";
		sql += "             FROM tdn_city";
		sql += "            WHERE vcitycode <> '0' AND vprovcode <> '00')";


		sql += " ORDER BY vprovcode, o";
		List cities_2 = query(sql,
				new Object[] {}, new RowMapper() {
					public Object mapRow(ResultSet rs, int i)
							throws SQLException {
						City city = new City();
						city.setShortName(rs.getString("VCITYSHORTNAME"));
						city.setCode(rs.getString("VCITYCODE"));
						city.setName(rs.getString("VCITYNAME"));
						city.setParentCode(rs.getString("VPROVCODE"));
						return city;
					}
				});
		return cities_2;
	}
	
	public List getDistricts() {	
		
		String sql = "";
		sql += " SELECT   *";
		sql += "     FROM (SELECT vdist_code, vdistname, vprovcode, vcitycode, vareacode,";
		sql += "                vzip_code, " + SqlUtils.toNumber("vdisporder")+ " o";
		sql += "             FROM tdn_district where vdist_code is not null "; 
		sql += " union all (select vcitycode, vcityname, vprovcode, vprovcode||'_'||vprovcode,'','',"+SqlUtils.toNumber("vdisporder")+"  from tdn_city a  where a.vprovcode in (select a.vprovcode  from tdn_province a  where visduchy='1' and vdisplay='1')  and vdisplay='1'))";
		sql += " ORDER BY vprovcode, vcitycode, o";
		List districts = query(sql,
				new Object[] {}, new RowMapper() {
					public Object mapRow(ResultSet rs, int i)
							throws SQLException {
						District district = new District();
						district.setAreaCode(rs.getString("VAREACODE"));
						district.setAreaCode(rs.getString("VZIP_CODE"));
						district.setCode(rs.getString("VDIST_CODE"));
						district.setName(rs.getString("VDISTNAME"));
						district.setParentCode(rs.getString("VCITYCODE"));
						return district;
					}
				});
		return districts;
	}
	public List getAreaManger(){
		String sql="select a.userid ,b.empname,c.roleid,decode(roleid,44,'02',47,'01')dealertype "
				   +" from auth_usermsg a,auth_emply b,auth_userrolerel c"
				   +"  where a.empid = b.empid"
				   +"  and a.userid = c.userid"
				   +"  and roleid in(44,47)";
		List result = query(sql,
				new Object[] {}, new RowMapper() {
					public Object mapRow(ResultSet rs, int i)
							throws SQLException {
						DictEntryListVo option = new DictEntryListVo();
						option.setCode(rs.getString("userid"));
						option.setName(rs.getString("empname"));
						option.setParentCode(rs.getString("dealertype"));
						return option;
					}
				});
		return result;
	}

	public List getDictionary(){
		String sql = "select * from t_dict_entry where status = '1' order by type_code, serial";
		List dict = query(sql, new Object[] {}, new RowMapper() {
			public Object mapRow(ResultSet rs, int i)throws SQLException {
				DictEntryListVo vo = new DictEntryListVo();
				vo.setId(RsUtils.getLong("id", rs));
				vo.setName(rs.getString("name"));
				vo.setCode(rs.getString("code"));
				vo.setParentCode(rs.getString("parent_code"));
				vo.setParentType(rs.getString("parent_type"));
				vo.setTypeCode(rs.getString("type_code"));
				vo.setSerial(RsUtils.getLong("serial", rs));
				return vo;
			}});
		return dict;
	}
	
	public List getRssc() {
		String sql = "";
		sql += " select * from tdn_rssc";
		List result = query(sql,
				new Object[] {}, new RowMapper() {
					public Object mapRow(ResultSet rs, int i)
							throws SQLException {
						Rssc o = new Rssc();
						o.setNameEn(rs.getString("VNAME_EN"));
//						o.setShortName(rs.getString("VBNAME"));

						o.setCode(rs.getString("VRSSC_ID"));
						o.setName(rs.getString("VNAME"));
						return o;
					}
				});
		return result;
	}
	
	public Object getUserEmployeeInfo(String userId){
		String sql="";
		
		sql+="select a.userid,b.empid,b.empname from auth_usermsg a,auth_emply b where a.empid = b.empid  and userid = "+userId;
		List result = this.query(sql, new RowMapper() {
					public Object mapRow(ResultSet rs, int i)
							throws SQLException {
						DictEntryListVo option = new DictEntryListVo();
						option.setCode(rs.getString("userid"));
						option.setName(rs.getString("empname"));
						return option;
					}
				});
		return result!=null&&result.size()>0?result.get(0):null;
	}
	
	public List getResources(){
		String sql1 = "select resource_id, role_id, rolename, roledesc  from tdn_recruit_res_role_rel a left join auth_rolemsg r on a.role_id = r.roleid where a.status = '1'";
		final List resourceRoleRelations = queryForList(sql1);
		
		String sql = "select * from tdn_recruit_resource where status = '1'";
		return query(sql, new RowMapper(){
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				Resource entity = new Resource();
				entity.setId(RsUtils.getLong("ID",rs));
				entity.setResourceName(rs.getString("RESOURCE_NAME"));
				entity.setTitle(rs.getString("TITLE"));
				entity.setResourceString(rs.getString("RESOURCE_STRING"));
				entity.setStatus(rs.getString("STATUS"));
				entity.setCreatedUser(RsUtils.getLong("CREATED_USER", rs));
				entity.setCreatedDate(rs.getDate("CREATED_DATE"));
				entity.setUpdatedUser(RsUtils.getLong("UPDATED_USER", rs));
				entity.setUpdatedDate(rs.getDate("UPDATED_DATE"));
				
				for (Iterator iterator = resourceRoleRelations.iterator(); iterator
						.hasNext();) {
					Map m = (Map) iterator.next();
					BigDecimal resourceId = (BigDecimal)m.get("resource_id");
					if(resourceId != null && resourceId.longValue() == entity.getId().longValue()){
						List roles = entity.getRoleNameList();
						if(roles == null) roles = new ArrayList();
						roles.add(m.get("rolename"));
						entity.setRoleNameList(roles);
					}
				}
				return entity;
			}});
	}
	
//	public List getRoles(){
//		String sql="select id,role_name,title,status,role_type,created_user,created_date,updated_user,updated_date  from sk_role a where a.status = '1' ";
//		return query(sql, new RoleListMapper());
//	}
}
